// Copyright 2008, Andrei Zinca
//
// Licensed under the Apache License, Version 2.0 (the "License");
// you may not use this file except in compliance with the License.
// You may obtain a copy of the License at
//
//     http://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.

import java.io.File;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.List;

import jxl.Sheet;
import jxl.Workbook;
import jxl.biff.CellReferenceHelper;

/**
 * Use the static assertEqualXLS() method to compare two .xls files
 */
public class XLSUnit {

	public static void assertEqualXLS(String expectedXLSFileName,
			String actualXLSFileName) {
		assertEqualXLS(expectedXLSFileName, actualXLSFileName,
				Collections.EMPTY_LIST, true);
	}

	public static void assertEqualXLS(String expectedXLSFileName,
			String actualXLSFileName, boolean showAllDifferences) {
		assertEqualXLS(expectedXLSFileName, actualXLSFileName,
				Collections.EMPTY_LIST, showAllDifferences);
	}

	public static void assertEqualXLS(String expectedXLSFileName,
			String actualXLSFileName, List<String> excludedWorksheetsList,
			boolean showAllDifferences) {

		List<String> messages = new ArrayList<String>();

		/*
		 * Open file
		 */
		File expectedXLSFile = new File(expectedXLSFileName);
		File actualXLSFile = new File(actualXLSFileName);

		/*
		 * Check files for existence
		 */
		checkFile(expectedXLSFile);
		checkFile(actualXLSFile);

		/*
		 * Open the Excel workbooks
		 */
		Workbook expectedWorkbook;
		Workbook actualWorkbook;
		try {
			expectedWorkbook = Workbook.getWorkbook(expectedXLSFile);
			actualWorkbook = Workbook.getWorkbook(actualXLSFile);
		} catch (Exception e) {
			throw new RuntimeException("Error when opening the Excel file.", e);
		}

		/*
		 * Get the Work sheet names
		 */
		String[] expectedWorksheetNames = expectedWorkbook.getSheetNames();
		String[] actualWorksheetNames = actualWorkbook.getSheetNames();

		/*
		 * Convert the Work sheet names array to a List and remove the excluded
		 * Work sheets. Those will be ignored during comparison
		 */
		List<String> expectedWorksheetNamesList = new ArrayList<String>(Arrays
				.asList(expectedWorksheetNames));
		List<String> actualWorksheetNamesList = new ArrayList<String>(Arrays
				.asList(actualWorksheetNames));

		expectedWorksheetNamesList.removeAll(excludedWorksheetsList);
		actualWorksheetNamesList.removeAll(excludedWorksheetsList);

		/*
		 * First condition is that both workbooks contain the same work sheets
		 */
		try {
			assertEqualWorksheetNames(expectedWorksheetNamesList,
					actualWorksheetNamesList, true);
		} catch (AssertionError e) {
			if (showAllDifferences) {
				messages.add(e.getMessage());
			} else {
				throw e;
			}
		}

		/*
		 * Compare one work sheet at a time
		 */
		for (String expectedWorksheetName : expectedWorksheetNamesList) {

			Sheet expectedWorksheet = expectedWorkbook
					.getSheet(expectedWorksheetName);
			Sheet actualWorksheet = actualWorkbook
					.getSheet(expectedWorksheetName);

			/*
			 * Work sheets must have same row count
			 */
			int expectedRowsNumber = expectedWorksheet.getRows();
			int actualRowsNumber = actualWorksheet.getRows();

			try {
				assertEqual(expectedRowsNumber, actualRowsNumber);
			} catch (AssertionError e) {
				String errMsg = "The number of rows in worksheet: "
						+ quote(expectedWorksheet.getName()) + " differ. "
						+ e.getMessage();
				if (showAllDifferences) {
					messages.add(errMsg);
				}
				throw new AssertionError(errMsg);
			}

			/*
			 * Work sheets must have the same column count
			 */
			int expectedColumnsNumber = expectedWorksheet.getColumns();
			int actualColumnsNumber = actualWorksheet.getColumns();

			try {
				assertEqual(expectedColumnsNumber, actualColumnsNumber);
			} catch (AssertionError e) {
				String errMsg = "The number of columns in worksheet: "
						+ quote(expectedWorksheet.getName()) + " differ. "
						+ e.getMessage();
				if (showAllDifferences) {
					messages.add(errMsg);
				} else {
					throw new AssertionError(errMsg);
				}
			}

			/*
			 * Iterate each cell in the current work sheet set
			 */
			for (int i = 0; i < expectedColumnsNumber; i++) {
				for (int j = 0; j < expectedRowsNumber; j++) {
					try {
						assertEqual(expectedWorksheet.getCell(i, j)
								.getContents(), actualWorksheet.getCell(i, j)
								.getContents());
					} catch (AssertionError e) {
						String errMsg = "The cell values at Row: "
								+ quote(j + 1)
								+ ", Column: "
								+ quote(CellReferenceHelper
										.getColumnReference(i))
								+ " in worksheet: "
								+ quote(expectedWorksheet.getName())
								+ " differ. " + e.getMessage();
						if (showAllDifferences) {
							messages.add(errMsg);
						} else {
							throw new AssertionError(errMsg);
						}
					}
				}
			}

			if (showAllDifferences && !messages.isEmpty()) {
				throw new AssertionError(messages);
			}

		}

	}

	/**
	 * Asserts the workbooks contain the same work sheets.<br>
	 * 
	 * @param expectedWorkbook
	 * @param actualWorkbook
	 */
	protected static void assertEqualWorksheetNames(
			List<String> expectedWorksheetNamesList,
			List<String> actualWorksheetNamesList, boolean ignoreOrder) {

		if (ignoreOrder) {
			Collections.sort(expectedWorksheetNamesList);
			Collections.sort(actualWorksheetNamesList);
		}

		if (!expectedWorksheetNamesList.equals(actualWorksheetNamesList)) {
			throw new AssertionError("Worksheet names differ. "
					+ showDiff(expectedWorksheetNamesList,
							actualWorksheetNamesList));
		}

	}

	/**
	 * Compares integers.
	 * 
	 * @param expected
	 * @param actual
	 */
	protected static void assertEqual(int expected, int actual) {
		if (expected != actual) {
			throw new AssertionError(showDiff(expected, actual));
		}
	}

	/**
	 * Compares strings.
	 * 
	 * @param expected
	 * @param actual
	 */
	protected static void assertEqual(String expected, String actual) {
		if (!expected.equals(actual)) {
			throw new AssertionError(showDiff(expected, actual));
		}
	}

	/**
	 * Check if file exists.
	 * 
	 * @param expected
	 * @param actual
	 */
	protected static void checkFile(File f) {
		if (!f.exists()) {
			throw new NullPointerException("The file "
					+ quote(f.getAbsolutePath()) + " was not found.");
		}
	}

	protected static String quote(Object value) {
		return "\"" + value + "\"";
	}

	protected static String showDiff(Object expected, Object actual) {
		return "Expected: " + quote(expected) + ". Actual: " + quote(actual)
				+ ".";
	}

}
